/*
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.    
*/

/*
 *	Copyright by Dejan Grujic 2004. http://www.cogin.com
 *  This code is provided as-is without waranties of any kind.
 *  You can copy, use and modify this code freely as long as you keep this copyright info.
 */
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

namespace DBConnection
{
    /// <summary>
    /// Custom Command Builder for MSSQL ONLY
    /// </summary>
	public class CustomCommandBuilder
	{
        /// <summary>
        /// DataTable ref
        /// </summary>
		DataTable dataTable;
        /// <summary>
        /// Connection ref
        /// </summary>
        SqlConnection connection;
        /// <summary>
        /// Actual Table Name
        /// </summary>
        string strDataTableName;

        /// <summary>
        /// Default Constructor
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        /// <param name="connection">Connection</param>
        /// <param name="strDataTableNameIn">Actual Table Name</param>
        public CustomCommandBuilder(DataTable dataTable, SqlConnection connection, string strDataTableNameIn)
		{
			this.dataTable = dataTable;
			this.connection = connection;
            this.strDataTableName = strDataTableNameIn;
		}

        /// <summary>
        /// Select all from the table associated
        /// </summary>
		public SqlCommand SelectAllCommand
		{
			get 
			{
				string commandText = "SELECT " + ColumnsString + " FROM " + TableName;
				return GetTextCommand( commandText );
			}
		}

        /// <summary>
        /// Select all with a where clause
        /// </summary>
        /// <param name="filter">The Where Clause </param>
        /// <returns>SqlCommand</returns>
		public SqlCommand GetSelectWithFilterCommand( string filter )
		{
			string commandText = "SELECT " + ColumnsString + 
							 " FROM " + TableName +
							 " WHERE " + filter;
			return GetTextCommand( commandText );
		}

        /// <summary>
        /// Select all with an order
        /// </summary>
        /// <param name="order">The order</param>
        /// <returns>SqlCommand</returns>
		public SqlCommand GetSelectWithOrderCommand( string order )
		{
			string commandText = "SELECT " + ColumnsString + 
				" FROM " + TableName +
				" ORDER BY " + order;
			return GetTextCommand( commandText );
		}

        /// <summary>
        /// DeleteCommand
        /// </summary>
		public SqlCommand DeleteCommand
		{
			get
			{
				SqlCommand command = GetTextCommand( "" );
				StringBuilder whereString = new StringBuilder();
				foreach( DataColumn column in dataTable.PrimaryKey )
				{
					if( whereString.Length > 0 )
					{
						whereString.Append( " AND " );
					}
					whereString.Append( column.ColumnName )
							.Append( " = @" ).Append( column.ColumnName );
					command.Parameters.Add( CreateParam( column ) );
				}
				string commandText = "DELETE FROM " + TableName
					+ " WHERE " + whereString.ToString();
				command.CommandText = commandText;
				return command;
			}
		}

		/// <summary>
		/// Creates Insert command with support for Autoincrement (Identity) tables
		/// </summary>
		public SqlCommand InsertCommand
		{
			get
			{
				SqlCommand command = GetTextCommand( "" );
				StringBuilder intoString = new StringBuilder();
				StringBuilder valuesString = new StringBuilder();
				ArrayList autoincrementColumns = AutoincrementKeyColumns;
				foreach( DataColumn column in dataTable.Columns )
				{
					if( ! autoincrementColumns.Contains( column ) )
					{
						// Not a primary key
						if( intoString.Length > 0 )
						{
							intoString.Append( ", " );
							valuesString.Append( ", " );
						}
						intoString.Append( column.ColumnName );
						valuesString.Append( "@" ).Append( column.ColumnName );
						command.Parameters.Add( CreateParam(column) );
					}
				}
				string commandText = "INSERT INTO " + TableName + "("
					+ intoString.ToString() + ") VALUES (" + valuesString.ToString() + "); ";
				if( autoincrementColumns.Count > 0 ) 
				{
					commandText += "SELECT SCOPE_IDENTITY() AS "
						+ ( (DataColumn) autoincrementColumns[0]) .ColumnName;
				}
				command.CommandText = commandText;
				return command;
			}
		}

		/// <summary>
		/// Creates Update command using only a Primary Key
		/// </summary>
        public SqlCommand UpdateCommand
		{
			get
			{
				SqlCommand command = GetTextCommand( "" );
				StringBuilder setString = new StringBuilder();
				StringBuilder whereString = new StringBuilder();
				DataColumn[] primaryKeyColumns = dataTable.PrimaryKey;
				foreach( DataColumn column in dataTable.Columns )
				{
					if( System.Array.IndexOf( primaryKeyColumns, column) != -1 )
					{
						// A primary key
						if( whereString.Length > 0 )
						{
							whereString.Append( " AND " );
						}
						whereString.Append( column.ColumnName )
								.Append( "= @old" ).Append( column.ColumnName );
					} 
					//else 
					//{
						if( setString.Length > 0 )
						{
							setString.Append( ", " );
						}
						setString.Append( column.ColumnName )
								.Append( " = @" ).Append( column.ColumnName );
					//}
					command.Parameters.Add( CreateParam( column ) );
					command.Parameters.Add( CreateOldParam( column ) );

				}
				string commandText = "UPDATE " + TableName + " SET "
					+ setString.ToString() + " WHERE " + whereString.ToString();
				command.CommandText = commandText;
				return command;
			}
		}

        /// <summary>
        /// Creates Update command with optimistic concurency support
        /// </summary>
        public SqlCommand UpdateCommandNotUsingPrimaryKey
        {
            get
            {
                SqlCommand command = GetTextCommand("");
                StringBuilder setString = new StringBuilder();
                StringBuilder whereString = new StringBuilder();
                DataColumn[] primaryKeyColumns = dataTable.PrimaryKey;
                foreach (DataColumn column in dataTable.Columns)
                {
                 //   if (System.Array.IndexOf(primaryKeyColumns, column) != -1)
                  //  {
                        // A primary key
                        if (whereString.Length > 0)
                        {
                            whereString.Append(" AND ");
                        }
                        whereString.Append(column.ColumnName)
                                .Append("= @old").Append(column.ColumnName);
                   // }
                   // else
                   // {
                        if (setString.Length > 0)
                        {
                            setString.Append(", ");
                        }
                        setString.Append(column.ColumnName)
                                .Append(" = @").Append(column.ColumnName);
                  //  }
                    command.Parameters.Add(CreateParam(column));
                    command.Parameters.Add(CreateOldParam(column));

                }
                string commandText = "UPDATE " + TableName + " SET "
                    + setString.ToString() + " WHERE " + whereString.ToString();
                command.CommandText = commandText;
                return command;
            }
        }

        /// <summary>
        /// Create the Old Parameter
        /// </summary>
        /// <param name="column">DataColumn</param>
        /// <returns>SqlParameter</returns>
		private SqlParameter CreateOldParam( DataColumn column )
		{
			SqlParameter sqlParam = new SqlParameter();
			string columnName = column.ColumnName;
			sqlParam.ParameterName = "@old" + columnName;
			sqlParam.SourceColumn = columnName;
			sqlParam.SourceVersion = DataRowVersion.Original;
			return sqlParam;
		}

        /// <summary>
        /// Determines what is an auto increment column in the table
        /// </summary>
		private ArrayList AutoincrementKeyColumns
		{
			get
			{
				ArrayList autoincrementKeys = new ArrayList();
				foreach( DataColumn primaryKeyColumn in dataTable.PrimaryKey )
				{
					if( primaryKeyColumn.AutoIncrement ) 
					{
						autoincrementKeys.Add( primaryKeyColumn );
					}
				}
				return autoincrementKeys;
			}
		}

        /// <summary>
        /// Creates a new parameter
        /// </summary>
        /// <param name="column">DataColumn</param>
        /// <returns>SqlParameter</returns>
		private SqlParameter CreateParam( DataColumn column )
		{
			SqlParameter sqlParam = new SqlParameter();
			string columnName = column.ColumnName;
			sqlParam.ParameterName = "@" + columnName;
			sqlParam.SourceColumn = columnName;
			return sqlParam;
		}

        /// <summary>
        /// Get a new SqlCommand object
        /// </summary>
        /// <param name="text">Sql Query</param>
        /// <returns>SqlCommand</returns>
		private SqlCommand GetTextCommand( string text )
		{
			SqlCommand command = new SqlCommand();
			command.CommandType = CommandType.Text;
			command.CommandText = text;
			command.Connection = connection;
			return command;
		}

        /// <summary>
        /// Get the table name for the table
        /// </summary>
		private string TableName 
		{
            get { return "[" + strDataTableName + "]"; }
		}

        /// <summary>
        /// Gets the column names
        /// </summary>
		private string ColumnsString
		{
			get 
			{
				StringBuilder columnsString = new StringBuilder();
				foreach( DataColumn column in dataTable.Columns )
				{
					if( columnsString.Length > 0 ) 
					{
						columnsString.Append( ", " );
					}
					columnsString.Append( column.ColumnName );
				}
				return columnsString.ToString();
			}
		}

	}
}
